package com.ph.irp.base.voucher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.voucher.dao.PBaseVoucherDAO;
import com.ph.irp.model.PBaseVoucher;
import com.ph.util.DBUtil;

public class PBaseVoucherDAOImpl implements PBaseVoucherDAO {

	private ComboPooledDataSource dataSource;

	private List<PBaseVoucher> voucherList;

	private PBaseVoucher voucherModel = null;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseVoucher> getVoucherList() {
		return voucherList;
	}

	public void setVoucherList(List<PBaseVoucher> voucherList) {
		this.voucherList = voucherList;
	}

	public PBaseVoucher getVoucherModel() {
		return voucherModel;
	}

	public void setVoucherModel(PBaseVoucher voucherModel) {
		this.voucherModel = voucherModel;
	}

	/*
	 * 新增凭证字记录定义
	 */
	@Override
	public void addVoucher(PBaseVoucher voucherModel) throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			// 新增一条记录
			StringBuilder sb = new StringBuilder();
			sb.append("insert into p_base_voucher ");
			sb
					.append(" (voucherName,debitY,creditY,debitOrCreditY,debitN,creditN,debitAndCreditN,isdefult,limitDC)");
			sb.append(" values (?,?,?,?,?,?,?,?,?)");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, voucherModel.getVoucherName());
			preparedStatement.setString(2, voucherModel.getDebitY());
			preparedStatement.setString(3, voucherModel.getCreditY());
			preparedStatement.setString(4, voucherModel.getDebitOrCreditY());
			preparedStatement.setString(5, voucherModel.getDebitN());
			preparedStatement.setString(6, voucherModel.getCreditN());
			preparedStatement.setString(7, voucherModel.getDebitAndCreditN());
			preparedStatement.setInt(8, voucherModel.getIsdefult());
			preparedStatement.setInt(9, voucherModel.getLimitDC());

			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/*
	 * 删除选中记录
	 */
	@Override
	public int deleteVoucher(Integer voucherId) throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int result = 0;

		try {
			connection = dataSource.getConnection();
			preparedStatement = connection
					.prepareStatement("delete from  p_base_voucher  where voucherId=?");

			PBaseVoucher voucherModel = new PBaseVoucher();
			voucherModel.setVoucherId(voucherId);

			preparedStatement.setInt(1, voucherModel.getVoucherId());

			preparedStatement.executeUpdate();
			result = 1;

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return result;
	}

	/*
	 * 查询凭证字记录
	 */
	@Override
	public List<PBaseVoucher> getAllVoucherList() throws Throwable {
		// TODO Auto-generated method stub
		voucherList = null;

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = dataSource.getConnection();
			preparedStatement = connection
					.prepareStatement(" select * from p_base_voucher");
			resultSet = preparedStatement.executeQuery();

			voucherList = this.turnResultSetToList(resultSet);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return voucherList;
	}

	/*
	 * 按照凭证字ID查询
	 */
	@Override
	public PBaseVoucher getVoucherByID(Integer voucherId) throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = dataSource.getConnection();
			preparedStatement = connection
					.prepareStatement("select * from p_base_voucher where voucherId=?");
			preparedStatement.setInt(1, voucherId);

			resultSet = preparedStatement.executeQuery();

			voucherList = this.turnResultSetToList(resultSet);
			voucherModel = voucherList.get(0);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return voucherModel;
	}

	/*
	 * 按照凭证字名称查询
	 */
	@Override
	public List<PBaseVoucher> getVoucherByName(String voucherName)
			throws Throwable {
		// TODO Auto-generated method stub
		voucherList = new ArrayList<PBaseVoucher>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = dataSource.getConnection();
			preparedStatement = connection
					.prepareStatement("select * from p_base_voucher where voucherName like ?");
			preparedStatement.setString(1, "%" + voucherName + "%");
			resultSet = preparedStatement.executeQuery();
			voucherList = this.turnResultSetToList(resultSet);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return voucherList;
	}

	/*
	 * 修改选中记录
	 */
	@Override
	public void updateVoucher(PBaseVoucher voucherModel) throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			// 根据ID修改记录
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_voucher set voucherName=?,debitY=? ,");
			sb
					.append("creditY=?,debitOrCreditY=?,debitN=?,creditN=?,debitAndCreditN=?,isdefult=?,limitDC=? ");
			sb.append(" where voucherId=?");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, voucherModel.getVoucherName());
			preparedStatement.setString(2, voucherModel.getDebitY());
			preparedStatement.setString(3, voucherModel.getCreditY());
			preparedStatement.setString(4, voucherModel.getDebitOrCreditY());
			preparedStatement.setString(5, voucherModel.getDebitN());
			preparedStatement.setString(6, voucherModel.getCreditN());
			preparedStatement.setString(7, voucherModel.getDebitAndCreditN());
			preparedStatement.setInt(8, voucherModel.getIsdefult());
			preparedStatement.setInt(9, voucherModel.getLimitDC());
			preparedStatement.setInt(10, voucherModel.getVoucherId());

			preparedStatement.executeUpdate();

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/*
	 * 将结果集resultSet封装返回list对象
	 * 
	 */
	public List<PBaseVoucher> turnResultSetToList(ResultSet resultSet)
			throws Throwable {
		List<PBaseVoucher> vl = new ArrayList<PBaseVoucher>();

		while (resultSet.next()) {
			PBaseVoucher vm = new PBaseVoucher();

			vm.setVoucherId(resultSet.getInt("voucherId"));
			vm.setVoucherName(resultSet.getString("voucherName"));
			vm.setDebitY(resultSet.getString("debitY"));
			vm.setCreditY(resultSet.getString("creditY"));
			vm.setDebitOrCreditY(resultSet
					.getString("debitOrCreditY"));
			vm.setDebitN(resultSet.getString("debitN"));
			vm.setCreditN(resultSet.getString("creditN"));
			vm.setDebitAndCreditN(resultSet
					.getString("debitAndCreditN"));
			vm.setIsdefult(resultSet.getInt("isDefult"));
			vm.setLimitDC(resultSet.getInt("limitDC"));

			vl.add(vm);
		}
		return vl;
	}
}
